package com.sample.db;

import java.util.ArrayList;
import java.util.List;

import com.sample.base.db.BaseDBHelper;
import com.sample.base.db.MyDBHelper;
import com.sample.entity.City;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.util.Log;

/**
 * 城市数据表操作类
 */
public class CityDBHelper extends BaseDBHelper<City>{

	public CityDBHelper(Context context) {
		super(context);
		// TODO Auto-generated constructor stub
	}
	@Override
	public synchronized long insert(int userId, City object) {
		// TODO Auto-generated method stub
		openDB();
		ContentValues contentValues=new ContentValues();
		contentValues.put(MyDBHelper.CITY_REGION_ID, object.getRegionID());
		contentValues.put(MyDBHelper.CITY_USER_ID, userId);
		contentValues.put(MyDBHelper.CITY_REGION_NAME, object.getRegionName());
		contentValues.put(MyDBHelper.CITY_LEVEL, object.getLevel());
		contentValues.put(MyDBHelper.CITY_PARENT_ID, object.getParentId());
		contentValues.put(MyDBHelper.CITY_DIPLAY_ORDER, object.getDisplayOrder());
		contentValues.put(MyDBHelper.CITY_ENABLE, object.getEnabled());
		contentValues.put(MyDBHelper.CITY_SEQUENCE, object.getSequence());
		long num=sqliteDatabase.insert(MyDBHelper.TABLE_CITY, null, contentValues);
		closeDB();
		return num;
	}

	@Override
	public long insert(int userId, List<City> objects){
		// TODO Auto-generated method stub
		openDB();
		long num=0;
		for(City object:objects){
			ContentValues contentValues=new ContentValues();
			contentValues.put(MyDBHelper.CITY_REGION_ID, object.getRegionID());
			contentValues.put(MyDBHelper.CITY_USER_ID, userId);
			contentValues.put(MyDBHelper.CITY_REGION_NAME, object.getRegionName());
			contentValues.put(MyDBHelper.CITY_LEVEL, object.getLevel());
			contentValues.put(MyDBHelper.CITY_PARENT_ID, object.getParentId());
			contentValues.put(MyDBHelper.CITY_DIPLAY_ORDER, object.getDisplayOrder());
			contentValues.put(MyDBHelper.CITY_ENABLE, object.getEnabled());
			contentValues.put(MyDBHelper.CITY_SEQUENCE, object.getSequence());
			sqliteDatabase.insert(MyDBHelper.TABLE_CITY, null, contentValues);
			num++;
			Log.i(TAG, "插入第:"+num+"行");
		}
		closeDB();
		return num;
	}

	@Override
	public int update(int userId, City object) {
		// TODO Auto-generated method stub
		openDB();
		ContentValues contentValues=new ContentValues();
		contentValues.put(MyDBHelper.CITY_REGION_ID, object.getRegionID());
		contentValues.put(MyDBHelper.CITY_REGION_NAME, object.getRegionName());
		contentValues.put(MyDBHelper.CITY_LEVEL, object.getLevel());
		contentValues.put(MyDBHelper.CITY_PARENT_ID, object.getParentId());
		contentValues.put(MyDBHelper.CITY_DIPLAY_ORDER, object.getDisplayOrder());
		contentValues.put(MyDBHelper.CITY_ENABLE, object.getEnabled());
		contentValues.put(MyDBHelper.CITY_SEQUENCE, object.getSequence());
		int n=sqliteDatabase.update(MyDBHelper.TABLE_CITY, contentValues, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_ID+"=?", new String[]{String.valueOf(userId),String.valueOf(object.getRegionID())});
		closeDB();
		return n;
	}

	@Override
	public int delete(int userId, long dataId) {
		// TODO Auto-generated method stub
		openDB();
		int n=sqliteDatabase.delete(MyDBHelper.TABLE_CITY, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_ID+"=?", new String[]{String.valueOf(userId),String.valueOf(dataId)});
		closeDB();
		return n;
	}
	@Override
	public int deleteAll(int userId) {
		// TODO Auto-generated method stub
		openDB();
		int n=sqliteDatabase.delete(MyDBHelper.TABLE_CITY, MyDBHelper.CITY_USER_ID+"=?", new String[]{String.valueOf(userId)});
		closeDB();
		return n;
	}
	@Override
	public List<City> queryAll(int userId) {
		// TODO Auto-generated method stub
		List<City> list=new ArrayList<City>();
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=?", new String[]{String.valueOf(userId)}, null, null, null);
			if(c!=null)
			{
				while(c.moveToNext())
				{
					City city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
					list.add(city);
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return list;
	}
	public List<City> queryByLevel(int userId,int level,boolean enabled) {
		// TODO Auto-generated method stub
		List<City> list=new ArrayList<City>();
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor c=null;
			if(enabled){
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_LEVEL+"=? and "+MyDBHelper.CITY_ENABLE+"=?", new String[]{String.valueOf(userId),String.valueOf(level),String.valueOf(enabled?1:0)}, null, null, MyDBHelper.CITY_SEQUENCE+" ASC");
			}else{
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_LEVEL+"=?", new String[]{String.valueOf(userId),String.valueOf(level)}, null, null, MyDBHelper.CITY_SEQUENCE+" ASC");
			}
			if(c!=null)
			{
				while(c.moveToNext())
				{
					City city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
					list.add(city);
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return list;
	}
	public List<City> queryByParentId(int userId,int level,int parentId,boolean enabled) {
		// TODO Auto-generated method stub
		List<City> list=new ArrayList<City>();
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor c=null;
			if(enabled){
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_LEVEL+"=? and "+MyDBHelper.CITY_PARENT_ID+"=? and "+MyDBHelper.CITY_ENABLE+"=?", new String[]{String.valueOf(userId),String.valueOf(level),String.valueOf(parentId),String.valueOf(enabled?1:0)}, null, null, null);
			}else{
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_LEVEL+"=? and "+MyDBHelper.CITY_PARENT_ID+"=?", new String[]{String.valueOf(userId),String.valueOf(level),String.valueOf(parentId)}, null, null, null);
			}
			if(c!=null)
			{
				while(c.moveToNext())
				{
					City city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
					list.add(city);
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return list;
	}
	@Override
	public int getSize(int userId) {
		// TODO Auto-generated method stub
		try{
			openDB();
			Cursor cursor=sqliteDatabase.rawQuery("SELECT COUNT(*) FROM "+MyDBHelper.TABLE_CITY+" WHERE "+MyDBHelper.CITY_USER_ID+"=?", new String[]{String.valueOf(userId)});
			if(cursor!=null)
			{
				int n=-1;
				while(cursor.moveToNext()){
					n=cursor.getInt(0);
				}
				cursor.close();
				return n;
			}

		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return -1;
	}

	@Override
	public City query(int userId, long dataId) {
		// TODO Auto-generated method stub
		City city=null;
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_ID+"=?", new String[]{String.valueOf(userId),String.valueOf(dataId)}, null, null, null);
			if(c!=null)
			{
				if(c.getCount()>1){
					Log.e(TAG, "数据异常,城市表中数据不唯一:userId="+userId+",regionId="+dataId);
					return null;
				}
				while(c.moveToNext())
				{
					city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return city;
	}
	public City query(int userId, long dataId,boolean enabled) {
		// TODO Auto-generated method stub
		City city=null;
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor c=null;
			if(enabled){
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_ID+"=? and "+MyDBHelper.CITY_ENABLE+"=?", new String[]{String.valueOf(userId),String.valueOf(dataId),String.valueOf(enabled?1:0)}, null, null, null);
			}else{
				c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_ID+"=?", new String[]{String.valueOf(userId),String.valueOf(dataId)}, null, null, null);
			}
			if(c!=null)
			{
				if(c.getCount()>1){
					Log.e(TAG, "数据异常,城市表中数据不唯一:userId="+userId+",regionId="+dataId);
					return null;
				}
				while(c.moveToNext())
				{
					city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return city;
	}
	public City queryByName(int userId,String name,int level) {
		// TODO Auto-generated method stub
		City city=null;
		try{
			openDB();
			String[] columns=new String[]{MyDBHelper.CITY_REGION_ID,MyDBHelper.CITY_USER_ID,MyDBHelper.CITY_REGION_NAME,MyDBHelper.CITY_LEVEL,MyDBHelper.CITY_PARENT_ID,MyDBHelper.CITY_DIPLAY_ORDER,MyDBHelper.CITY_ENABLE};
			Cursor 	c = sqliteDatabase.query(MyDBHelper.TABLE_CITY, columns, MyDBHelper.CITY_USER_ID+"=? and "+MyDBHelper.CITY_REGION_NAME+" like ? and "+MyDBHelper.CITY_ENABLE+"=? and "+MyDBHelper.CITY_LEVEL+"=?", new String[]{String.valueOf(userId),name,String.valueOf(1),String.valueOf(level)}, null, null, null);
			if(c!=null)
			{
				if(c.getCount()>1){
					Log.e(TAG, "数据异常,城市表中数据不唯一:userId="+userId);
					return null;
				}
				while(c.moveToNext())
				{
					city=new City();
					city.setRegionID(c.getInt(c.getColumnIndex(MyDBHelper.CITY_REGION_ID)));
					city.setUserId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_USER_ID)));
					city.setRegionName(c.getString(c.getColumnIndex(MyDBHelper.CITY_REGION_NAME)));
					city.setLevel(c.getInt(c.getColumnIndex(MyDBHelper.CITY_LEVEL)));
					city.setParentId(c.getInt(c.getColumnIndex(MyDBHelper.CITY_PARENT_ID)));
					city.setDisplayOrder(c.getInt(c.getColumnIndex(MyDBHelper.CITY_DIPLAY_ORDER)));
					city.setEnabled(c.getInt(c.getColumnIndex(MyDBHelper.CITY_ENABLE)));
				}
			}
			c.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			closeDB();
		}
		return city;
	}
}
